This disclosure relates generally to query processing in a data processing system and more specifically to generating filter expressions using tree parameters to modify a query in a data processing system.
Typical drill-through implementations using some form of query system continue to challenge end users. As more data is available for processing using queries there will be even more pressure to simplify the drill-through experience both for authors and consumers. For example, when dealing with relational data, there are typically too many parameters required to support drill-through to reports based on relationally modeled data. In another example, multiple selections in a source report can result in too much data in a target report after a drill-through operation.
By way of explanation, the following terminology has been used to clarify concepts in the specification. A parameter is a named placeholder that can be used in expressions. A parameter is used like a formal argument for a function in a programming language. Drill-through operations use fitter expressions to compare a parameter to a query item to filter data. A parameter value is a value assigned to a parameter at runtime. A parameter value may be specified in a number of ways, including hut not limited to: a scalar value (match if the query item value is equal to the parameter value, for example, queryItem=5); a bound range (match if the query item value is within the endpoints specified by the bound range, for example, I<=queryItem<=5), an unbound range (match if the query item is within the unbound range specified by the endpoint, for example, queryItem>5), and member unique names (MUN) that identify a member in an online analytical processing (OLAP) data source (match if the MUN of the member is equal to the specified MUN). A prompt is a set of user interface elements used to obtain a parameter value for a parameter.
In a normalized relational system, tables represent the various levels from the dimensional model. When linked to a table containing facts, these tables are organized into a snowflake. De-normalization of the level tables is performed to produce a star pattern which results in more efficient database processing. Each level table has one or more keys based on one or more columns. The key columns identify level members as well as provide parent and child associations by foreign key relationships. Some columns are identified as business keys while other columns are considered business labels.
To fully support the hierarchical structure in a report that is a drill-through target, a parameter is required for each business key. For example, in a four level hierarchical structure including country identifier, state identifier, municipality identifier and property identifier, four parameters are required for this simple hierarchy. Consider the challenges faced by authors when faced with applications supporting ten or more hierarchies. Each parameter is created using a filter expression such as: ‘StateID in ?StateID?’, in which StateID is the query item, and ?StateID? is the parameter. These filters are then combined using an AND operation during the query planning stage to select the appropriate set of rows. This approach to hierarchical structures in relationally modeled systems leads to considerable overhead for authors creating reports that support a wide range of drill-through scenarios. Proposed solutions for constructing filters based on concatenating identifier values prior to comparing identifiers works only when selecting a known classification. The problem with current technology is there is no way to reflect correlations between parameter values when building the filter expression.